/*
 * PKL 51 GUNUNG KIDUL
 * Subseksie DATA ENTRY + CAPI
 */

package pkl51.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import pkl51.database.DatabaseUtility;
import pkl51.entity.Blok3;
import pkl51.error.EntityException;
import pkl51.service.Blok3Dao;

/**
 *
 * @author thiolutfi
 */
public class Blok3DaoImpl implements Blok3Dao {

    private Connection connection;
    private final String insertBlok3 = "INSERT INTO blok3 VALUES (?, ?, ?, ?, ?, ?, ?)ON DUPLICATE KEY UPDATE B3R1 = ?, B3R2a = ?, B3R2b = ?, B3R4 = ?, B3R5a = ?, B3R5b = ?";
    private final String updateBlok3 = "UPDATE blok3 SET B3R1 = ?, B3R2a = ?, B3R2b = ?, B3R4 = ?, B3R5a = ?, B3R5b = ? WHERE NKS = ?";
    private final String getBlok3ByNKS = "SELECT * FROM blok3 WHERE NKS = ?";

    @Override
    public void insertBlok3(Blok3 blok3) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(insertBlok3);

            statement.setString(1, blok3.getNKS());
            statement.setString(2, blok3.getB3R1());
            statement.setString(3, blok3.getB3R2a());
            statement.setString(4, blok3.getB3R2b());
            statement.setString(5, blok3.getB3R4());
            statement.setString(6, blok3.getB3R5a());
            statement.setString(7, blok3.getB3R5b());
            statement.setString(8, blok3.getB3R1());
            statement.setString(9, blok3.getB3R2a());
            statement.setString(10, blok3.getB3R2b());
            statement.setString(11, blok3.getB3R4());
            statement.setString(12, blok3.getB3R5a());
            statement.setString(13, blok3.getB3R5b());
            
            statement.executeUpdate();

            connection.commit();

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new EntityException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    @Override
    public void updateBlok3(Blok3 blok3) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(updateBlok3);

            statement.setString(1, blok3.getB3R1());
            statement.setString(2, blok3.getB3R2a());
            statement.setString(3, blok3.getB3R2b());
            statement.setString(4, blok3.getB3R4());
            statement.setString(5, blok3.getB3R5a());
            statement.setString(6, blok3.getB3R5b());
            statement.setString(7, blok3.getNKS());
            
            statement.executeUpdate();

            connection.commit();

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new EntityException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    @Override
    public Blok3 getBlok3ByNKS(String NKS) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getBlok3ByNKS);
            statement.setString(1, NKS);

            ResultSet result = statement.executeQuery();
            Blok3 blok3 = null;
            if (result.next()) {
                blok3 = new Blok3();
                blok3.setB3R1(result.getString("B3R1"));
                blok3.setB3R2a(result.getString("B3R2a"));
                blok3.setB3R2b(result.getString("B3R2b"));
                blok3.setB3R4(result.getString("B3R4"));
                blok3.setB3R5a(result.getString("B3R5a"));
                blok3.setB3R5b(result.getString("B3R5b"));
            } else {
                throw new EntityException("Hasil untuk NKS: "+NKS+" tidak ditemukan");
            }
            connection.commit();
            return blok3;
        } catch (SQLException exception) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, exception);
            throw new EntityException(exception.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exception) {
                    Logger.getLogger(Blok3DaoImpl.class.getName()).log(Level.SEVERE, null, exception);
                }
            }
        }
    }

}
